﻿USE [master]
GO
/****** Object:  Database [GameServicesDB]    Script Date: 04/17/2012 00:38:15 ******/
CREATE DATABASE [GameServicesDB]
GO
ALTER DATABASE [GameServicesDB] SET COMPATIBILITY_LEVEL = 100
GO

USE [GameServicesDB]
GO

CREATE SCHEMA [games] AUTHORIZATION [dbo]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [games].[Games](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[GameHash] [nvarchar](50) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[NormalizedName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Games] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [games].[GameResult](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[GameId] [int] NOT NULL,
	[Score] [int] NOT NULL,
	[DateTime] [datetime] NOT NULL,
	[PlayerName] [nvarchar](50) NOT NULL,
	[DeviceId] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_GameResult] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [games].[GameResultsLightView]
AS
SELECT     ROW_NUMBER() OVER ( PARTITION BY gr.GameId ORDER BY gr.Score DESC, gr.[DateTime] DESC) AS [Id], gr.[GameId], gr.[Score], gr.[DateTime], gr.[PlayerName], g.[NormalizedName], 
g.[Name]
FROM         games.GameResult AS gr INNER JOIN
                      games.Games AS g ON gr.GameId = g.Id
GO


ALTER TABLE [games].[GameResult] ADD  DEFAULT (getdate()) FOR [DateTime]
GO


ALTER TABLE [games].[GameResult]  WITH CHECK ADD  CONSTRAINT [FK_GameResult_Games] FOREIGN KEY([GameId])
REFERENCES [games].[Games] ([Id])
GO
ALTER TABLE [games].[GameResult] CHECK CONSTRAINT [FK_GameResult_Games]
GO
